// backend/api/database.js
const express = require('express');
const mysql = require('mysql2/promise');
const router = express.Router();

// 数据库连接配置
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: '12345678',
  database: 'Test'
};

// 获取所有表名和字段信息
router.get('/api/tables', async (req, res) => {
  try {
    const connection = await mysql.createConnection(dbConfig);
    
    // 1. 获取所有表名
    const [tables] = await connection.query(`
      SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = ?
    `, [dbConfig.database]);
    
    // 2. 为每个表获取字段信息
    const tableList = [];
    for (const table of tables) {
      const tableName = table.TABLE_NAME;
      
      // 获取该表的所有字段
      const [columns] = await connection.query(`
        SELECT COLUMN_NAME, DATA_TYPE 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION
      `, [dbConfig.database, tableName]);
      
      tableList.push({
        name: tableName,
        icon: getTableIcon(tableName), // 根据表名分配图标
        fields: columns.map(col => col.COLUMN_NAME),
        fieldTypes: columns.reduce((acc, col) => {
          acc[col.COLUMN_NAME] = col.DATA_TYPE;
          return acc;
        }, {})
      });
    }
    
    await connection.end();
    
    res.json({
      success: true,
      tables: tableList
    });
    
  } catch (error) {
    console.error('获取表信息失败:', error);
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// 根据表名智能分配图标
function getTableIcon(tableName) {
  const name = tableName.toLowerCase();
  if (name.includes('user') || name.includes('patient') || name.includes('患者')) return '👤';
  if (name.includes('doctor') || name.includes('医生')) return '👨‍⚕️';
  if (name.includes('order') || name.includes('处方')) return '💊';
  if (name.includes('department') || name.includes('科室')) return '🏥';
  if (name.includes('record') || name.includes('记录')) return '📋';
  return '📊'; // 默认图标
}

module.exports = router;